﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EasyCLRBase;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using EasyCLRBase.AssemblyExplorer;
using System.Data.SqlClient;
using System.Data;

namespace EasyCLRMisc
{
    [Export(typeof(EasyCLRBase.IClrBase))]
    public class Procedures : ClrBase
    {
        /// <summary>
        /// Since print will not print nvarchar over 4000 characters, this method will be very usefull for printing long String.
        /// </summary>
        /// <param name="print">String to print</param>
        [SqlProcedure(Name = "ext_misc_big_print")]
        public static void ClrBigPrint(SqlString print, SqlBoolean silent)
        {
            Boolean Error = false;
            if (print.IsNull == false)
            {
                foreach (String item in print.Value.Split(new String[1] { "\r\n" }, StringSplitOptions.None))
                    if (item.Length > 4000)
                    {
                        Error = true;
                        for (int i = 0; i < item.Length; i = i + 4000)
                        {
                            int max = i + 4000,
                                start = i,
                                end = 4000;
                            if (max > item.Length)
                                max = Math.Abs(i - item.Length);
                            end = (max < end) ? max : end;
                            Char[] val = new Char[end];
                            item.CopyTo(start, val, 0, end);
                            if (SqlContext.IsAvailable)
                                SqlContext.Pipe.Send(new String(val));
                        }
                    }
                    else
                        if (SqlContext.IsAvailable)
                            SqlContext.Pipe.Send(item);
            }
            if (Error == true && (silent.IsNull == true || silent.IsFalse == true))
                throw new ArgumentOutOfRangeException("print", "Cannot print correctly the String. The line length is over 4000 characters. All characters will be printed but they will be some new line breaks.");
        }
        /// <summary>
        /// Execute command line.
        /// </summary>
        /// <param name="cmd">Commandline command to execute</param>
        /// <param name="output">Commandline result.</param>
        [SqlProcedure(Name = "ext_misc_cmd")]
        public static void ClrExecuteCmd(SqlString cmd, [ClrParameter(Result = "NVARCHAR(MAX) OUTPUT")] out SqlString output)
        {
            if (cmd.IsNull)
                throw new ArgumentException("Command line empty");

            var startInfo = new System.Diagnostics.ProcessStartInfo("cmd.exe", String.Concat("/C \"", cmd, "\""));
            String error = null;
            startInfo.CreateNoWindow = true;
            startInfo.UseShellExecute = false;
            startInfo.RedirectStandardOutput = true;
            startInfo.RedirectStandardError = true;
            startInfo.WorkingDirectory = Environment.GetEnvironmentVariable("SystemRoot");

            using (var process = new System.Diagnostics.Process())
            {
                process.StartInfo = startInfo;
                process.Start();

                output = process.StandardOutput.ReadToEnd();
                error = process.StandardError.ReadToEnd();
            }
        }
        /*
        [SqlProcedure(Name = "ext_compress")]
        public static void IonicZipCompress(
            SqlString zipFileName,
            SqlString entriesToCompress,
            [ClrParameter(Result = "NVARCHAR(4000) = NULL"), SqlFacet(MaxSize = -1)]SqlString password,
            [ClrParameter(Result = "NVARCHAR(4000) = NULL"), SqlFacet(MaxSize = -1)]SqlString comment,
            [ClrParameter(Result = "INT = 9")]SqlInt32 compressionLevel,
            [ClrParameter(Result = "INT = NULL")]SqlInt32 compressionMethod,
            [ClrParameter(Result = "INT = NULL")]SqlInt32 encryptionAlgorithm,
            [ClrParameter(Result = "BIT = 1")]SqlBoolean overwriteIfExists)
        {
            if (overwriteIfExists.IsNull == true || overwriteIfExists.IsTrue)
                System.IO.File.Delete(zipFileName.Value);
            using (var zip = new Ionic.Zip.ZipFile(zipFileName.Value))
            {
                if (compressionLevel.IsNull == false)
                    zip.CompressionLevel = (Ionic.Zlib.CompressionLevel)compressionLevel.Value;
                if (compressionMethod.IsNull == false)
                    zip.CompressionMethod = (Ionic.Zip.CompressionMethod)compressionMethod.Value;
                if (comment.IsNull == false)
                    zip.Comment = comment.Value;
                if (encryptionAlgorithm.IsNull == false)
                    zip.Encryption = (Ionic.Zip.EncryptionAlgorithm)encryptionAlgorithm.Value;
                if (password.IsNull == false)
                    zip.Password = password.Value;
                foreach (String entry in entriesToCompress.Value.Split(new String[1] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
                {
                    if (System.IO.File.Exists(entry) == true)
                        zip.AddFile(entry);
                    else if (System.IO.Directory.Exists(entry) == true)
                        zip.AddDirectory(entry);
                }
                zip.Save();
            }
        }*/

        //[SqlTrigger(Event = "FOR INSERT, UPDATE, DELETE", Name = "ext_misc_test_trigger", Target = "[dbo].[test]")]
        public static void ClrTriggerTest()
        {
            var trigger = SqlContext.TriggerContext;
            var sb = new SqlConnectionStringBuilder() { ContextConnection = true };
            using (var con = new SqlConnection(sb.ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand(null, con))
                {
                    switch (trigger.TriggerAction)
                    {
                        case TriggerAction.Insert:
                        case TriggerAction.Update:
                            cmd.CommandText = String.Concat("SELECT '", trigger, "' as action id FROM INSERTED");
                            break;
                        case TriggerAction.Delete:
                            cmd.CommandText = "SELECT 'deleted' as action id FROM DELETED";
                            break;
                    }
                    var ds = new DataSet();
                    using (var da = new System.Data.SqlClient.SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                    cmd.CommandText = "insert into test2(text) values(@text)";
                    SqlParameter p = null;
                    Boolean prepared = false;
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            Object pValue = String.Concat(row[0], " ", row[1]);
                            if (p == null)
                                p = cmd.Parameters.AddWithValue("@text", pValue);
                            else
                                p.Value = pValue;
                            if(prepared==false)
                            {
                                cmd.Prepare();
                                prepared = true;
                            }
                        }
                    }
                }
            }
        }
    }
}
